/**
 * 数据完整性验证工具
 * 用于验证数据库的完整性和一致性
 */

import mysql from 'mysql2/promise'
import crypto from 'crypto'
import fs from 'fs/promises'
import path from 'path'

class DatabaseIntegrityValidator {
  constructor(config) {
    this.config = config
    this.connection = null
    this.validationResults = []
    this.checksums = new Map()
  }

  async connect() {
    try {
      this.connection = await mysql.createConnection(this.config)
      console.log('✅ 数据库连接成功')
      return true
    } catch (error) {
      console.error('❌ 数据库连接失败:', error.message)
      return false
    }
  }

  async disconnect() {
    if (this.connection) {
      await this.connection.end()
      this.connection = null
    }
  }

  /**
   * 执行所有完整性检查
   */
  async validateAll() {
    console.log('🔍 开始数据库完整性验证...')

    const validations = [
      this.validateTableStructure,
      this.validateForeignKeys,
      this.validateUniqueConstraints,
      this.validateDataConsistency,
      this.validateChecksums,
      this.validateAuditLogs,
      this.validateBackupIntegrity,
      this.validatePerformanceMetrics
    ]

    for (const validation of validations) {
      try {
        await validation.call(this)
      } catch (error) {
        this.addResult('ERROR', validation.name, error.message)
      }
    }

    return this.generateReport()
  }

  /**
   * 验证表结构
   */
  async validateTableStructure() {
    console.log('📋 验证表结构...')

    const expectedTables = [
      'doctors',
      'patients',
      'scoring_tasks',
      'scoring_results',
      'audit_logs'
    ]

    // 检查表是否存在
    const [existingTables] = await this.connection.execute('SHOW TABLES')
    const tableNames = existingTables.map(row => Object.values(row)[0])

    for (const expectedTable of expectedTables) {
      if (tableNames.includes(expectedTable)) {
        this.addResult('PASS', 'Table existence', `表 ${expectedTable} 存在`)

        // 检查表结构
        await this.validateTableColumns(expectedTable)
      } else {
        this.addResult('FAIL', 'Table existence', `表 ${expectedTable} 不存在`)
      }
    }

    // 检查是否有意外的表
    const unexpectedTables = tableNames.filter(name => !expectedTables.includes(name))
    if (unexpectedTables.length > 0) {
      this.addResult('WARN', 'Unexpected tables', `发现意外表: ${unexpectedTables.join(', ')}`)
    }
  }

  /**
   * 验证表列结构
   */
  async validateTableColumns(tableName) {
    const [columns] = await this.connection.execute(`DESCRIBE ${tableName}`)

    // 检查必需的列
    const requiredColumns = this.getRequiredColumns(tableName)
    const columnNames = columns.map(col => col.Field)

    for (const requiredCol of requiredColumns) {
      if (columnNames.includes(requiredCol.name)) {
        // 验证列类型
        const column = columns.find(col => col.Field === requiredCol.name)
        if (column.Type.match(requiredCol.typePattern)) {
          this.addResult('PASS', 'Column type', `${tableName}.${requiredCol.name} 类型正确`)
        } else {
          this.addResult('FAIL', 'Column type', `${tableName}.${requiredCol.name} 类型错误: 期望 ${requiredCol.typePattern}, 实际 ${column.Type}`)
        }

        // 验证是否允许NULL
        if (requiredCol.nullable === false && column.Null === 'NO') {
          this.addResult('PASS', 'Column nullable', `${tableName}.${requiredCol.name} 正确设置为NOT NULL`)
        } else if (requiredCol.nullable === false && column.Null === 'YES') {
          this.addResult('FAIL', 'Column nullable', `${tableName}.${requiredCol.name} 应该设置为NOT NULL`)
        }
      } else {
        this.addResult('FAIL', 'Column existence', `表 ${tableName} 缺少必需列: ${requiredCol.name}`)
      }
    }
  }

  /**
   * 获取表的必需列定义
   */
  getRequiredColumns(tableName) {
    const schemas = {
      doctors: [
        { name: 'id', typePattern: 'int', nullable: false },
        { name: 'username', typePattern: 'varchar', nullable: false },
        { name: 'password', typePattern: 'varchar', nullable: false },
        { name: 'name', typePattern: 'varchar', nullable: false },
        { name: 'role', typePattern: 'int', nullable: false },
        { name: 'email', typePattern: 'varchar', nullable: false },
        { name: 'created_at', typePattern: 'timestamp', nullable: false }
      ],
      patients: [
        { name: 'id', typePattern: 'int', nullable: false },
        { name: 'patient_code', typePattern: 'varchar', nullable: false },
        { name: 'created_at', typePattern: 'timestamp', nullable: false }
      ],
      scoring_tasks: [
        { name: 'id', typePattern: 'int', nullable: false },
        { name: 'patient_code', typePattern: 'varchar', nullable: false },
        { name: 'status', typePattern: 'int', nullable: false },
        { name: 'doctor_id', typePattern: 'int', nullable: false },
        { name: 'created_at', typePattern: 'timestamp', nullable: false },
        { name: 'updated_at', typePattern: 'timestamp', nullable: false }
      ],
      scoring_results: [
        { name: 'id', typePattern: 'int', nullable: false },
        { name: 'task_id', typePattern: 'int', nullable: false },
        { name: 'score', typePattern: 'tinyint', nullable: false },
        { name: 'severity', typePattern: 'tinyint', nullable: false },
        { name: 'confidence', typePattern: 'decimal', nullable: false },
        { name: 'created_at', typePattern: 'timestamp', nullable: false }
      ],
      audit_logs: [
        { name: 'id', typePattern: 'int', nullable: false },
        { name: 'user_id', typePattern: 'int', nullable: false },
        { name: 'action', typePattern: 'varchar', nullable: false },
        { name: 'table_name', typePattern: 'varchar', nullable: false },
        { name: 'created_at', typePattern: 'timestamp', nullable: false }
      ]
    }

    return schemas[tableName] || []
  }

  /**
   * 验证外键约束
   */
  async validateForeignKeys() {
    console.log('🔗 验证外键约束...')

    const expectedForeignKeys = [
      {
        table: 'scoring_tasks',
        column: 'doctor_id',
        references: 'doctors.id'
      },
      {
        table: 'scoring_results',
        column: 'task_id',
        references: 'scoring_tasks.id'
      }
    ]

    for (const fk of expectedForeignKeys) {
      // 检查外键约束是否存在
      const [constraints] = await this.connection.execute(
        `SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
         WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?
         AND REFERENCED_TABLE_NAME IS NOT NULL`,
        [this.config.database, fk.table, fk.column]
      )

      if (constraints.length > 0) {
        this.addResult('PASS', 'Foreign key', `${fk.table}.${fk.column} 外键约束存在`)

        // 检查数据完整性
        await this.checkForeignKeyDataIntegrity(fk)
      } else {
        this.addResult('FAIL', 'Foreign key', `${fk.table}.${fk.column} 缺少外键约束`)
      }
    }

    // 检查孤立数据
    await this.checkOrphanedData()
  }

  /**
   * 检查外键数据完整性
   */
  async checkForeignKeyDataIntegrity(fk) {
    // 检查是否存在无效的外键引用
    const [orphanedRecords] = await this.connection.execute(
      `SELECT COUNT(*) as count FROM ${fk.table} t
       LEFT JOIN ${fk.references.split('.')[0]} r ON t.${fk.column} = r.${fk.references.split('.')[1]}
       WHERE r.${fk.references.split('.')[1]} IS NULL`,
    )

    const orphanCount = orphanedRecords[0].count
    if (orphanCount > 0) {
      this.addResult('FAIL', 'Data integrity', `${fk.table}.${fk.column} 存在 ${orphanCount} 条孤立记录`)
    } else {
      this.addResult('PASS', 'Data integrity', `${fk.table}.${fk.column} 无孤立记录`)
    }
  }

  /**
   * 检查孤立数据
   */
  async checkOrphanedData() {
    // 检查没有对应任务的结果
    const [orphanedResults] = await this.connection.execute(
      `SELECT COUNT(*) as count FROM scoring_results sr
       LEFT JOIN scoring_tasks st ON sr.task_id = st.id
       WHERE st.id IS NULL`
    )

    if (orphanedResults[0].count > 0) {
      this.addResult('FAIL', 'Orphaned data', `存在 ${orphanedResults[0].count} 条孤立的结果记录`)
    }
  }

  /**
   * 验证唯一约束
   */
  async validateUniqueConstraints() {
    console.log('🔑 验证唯一约束...')

    const uniqueConstraints = [
      { table: 'doctors', columns: ['username'] },
      { table: 'doctors', columns: ['email'] },
      { table: 'patients', columns: ['patient_code'] }
    ]

    for (const constraint of uniqueConstraints) {
      await this.checkUniqueConstraint(constraint)
    }
  }

  /**
   * 检查唯一约束
   */
  async checkUniqueConstraint(constraint) {
    const columnsStr = constraint.columns.join(', ')
    const [duplicates] = await this.connection.execute(
      `SELECT ${columnsStr}, COUNT(*) as count
       FROM ${constraint.table}
       GROUP BY ${columnsStr}
       HAVING count > 1`
    )

    if (duplicates.length > 0) {
      this.addResult('FAIL', 'Unique constraint',
        `${constraint.table}(${columnsStr}) 存在 ${duplicates.length} 组重复数据`)
    } else {
      this.addResult('PASS', 'Unique constraint',
        `${constraint.table}(${columnsStr}) 无重复数据`)
    }
  }

  /**
   * 验证数据一致性
   */
  async validateDataConsistency() {
    console.log('📊 验证数据一致性...')

    // 检查评分范围
    await this.checkScoreRanges()

    // 检查状态值
    await this.checkStatusValues()

    // 检查时间戳一致性
    await this.checkTimestampConsistency()

    // 检查角色值
    await this.checkRoleValues()
  }

  /**
   * 检查评分范围
   */
  async checkScoreRanges() {
    const [invalidScores] = await this.connection.execute(
      'SELECT COUNT(*) as count FROM scoring_results WHERE score < 0 OR score > 8'
    )

    if (invalidScores[0].count > 0) {
      this.addResult('FAIL', 'Score range', `存在 ${invalidScores[0].count} 条超出范围的评分`)
    } else {
      this.addResult('PASS', 'Score range', '所有评分都在有效范围内 (0-8)')
    }

    // 检查严重程度级别
    const [invalidSeverities] = await this.connection.execute(
      'SELECT COUNT(*) as count FROM scoring_results WHERE severity < 1 OR severity > 4'
    )

    if (invalidSeverities[0].count > 0) {
      this.addResult('FAIL', 'Severity range', `存在 ${invalidSeverities[0].count} 条无效的严重程度`)
    } else {
      this.addResult('PASS', 'Severity range', '所有严重程度都在有效范围内 (1-4)')
    }
  }

  /**
   * 检查状态值
   */
  async checkStatusValues() {
    const [invalidStatus] = await this.connection.execute(
      'SELECT COUNT(*) as count FROM scoring_tasks WHERE status NOT IN (1, 2, 3, 4)'
    )

    if (invalidStatus[0].count > 0) {
      this.addResult('FAIL', 'Status values', `存在 ${invalidStatus[0].count} 条无效的状态值`)
    } else {
      this.addResult('PASS', 'Status values', '所有状态值都有效')
    }
  }

  /**
   * 检查时间戳一致性
   */
  async checkTimestampConsistency() {
    // 检查created_at是否为空
    const [nullCreated] = await this.connection.execute(
      `SELECT table_name, COUNT(*) as count FROM information_schema.columns
       WHERE table_schema = ? AND column_name = 'created_at' AND is_nullable = 'YES'
       GROUP BY table_name`,
      [this.config.database]
    )

    if (nullCreated.length > 0) {
      this.addResult('FAIL', 'Timestamp consistency',
        `存在允许NULL的created_at字段: ${nullCreated.map(r => r.table_name).join(', ')}`)
    }

    // 检查updated_at是否在created_at之后
    const [invalidTimestamps] = await this.connection.execute(
      'SELECT COUNT(*) as count FROM scoring_tasks WHERE updated_at < created_at'
    )

    if (invalidTimestamps[0].count > 0) {
      this.addResult('FAIL', 'Timestamp consistency',
        `存在 ${invalidTimestamps[0].count} 条updated_at早于created_at的记录`)
    } else {
      this.addResult('PASS', 'Timestamp consistency', '时间戳顺序正确')
    }
  }

  /**
   * 检查角色值
   */
  async checkRoleValues() {
    const [invalidRoles] = await this.connection.execute(
      'SELECT COUNT(*) as count FROM doctors WHERE role NOT IN (1, 2, 3)'
    )

    if (invalidRoles[0].count > 0) {
      this.addResult('FAIL', 'Role values', `存在 ${invalidRoles[0].count} 条无效的角色值`)
    } else {
      this.addResult('PASS', 'Role values', '所有角色值都有效')
    }
  }

  /**
   * 计算和验证校验和
   */
  async validateChecksums() {
    console.log('🔐 计算数据校验和...')

    const tables = ['doctors', 'patients', 'scoring_tasks', 'scoring_results']

    for (const table of tables) {
      try {
        const checksum = await this.calculateTableChecksum(table)
        this.checksums.set(table, checksum)
        this.addResult('INFO', 'Checksum', `${table} 校验和: ${checksum}`)
      } catch (error) {
        this.addResult('FAIL', 'Checksum', `计算 ${table} 校验和失败: ${error.message}`)
      }
    }

    // 保存校验和到文件
    await this.saveChecksums()
  }

  /**
   * 计算表校验和
   */
  async calculateTableChecksum(tableName) {
    const [rows] = await this.connection.execute(`SELECT * FROM ${tableName} ORDER BY id`)

    const dataString = JSON.stringify(rows)
    return crypto.createHash('sha256').update(dataString).digest('hex')
  }

  /**
   * 保存校验和
   */
  async saveChecksums() {
    const checksumData = {
      timestamp: new Date().toISOString(),
      database: this.config.database,
      checksums: Object.fromEntries(this.checksums)
    }

    try {
      await fs.writeFile(
        path.join(process.cwd(), 'database_checksums.json'),
        JSON.stringify(checksumData, null, 2)
      )
      this.addResult('PASS', 'Checksum save', '校验和已保存到文件')
    } catch (error) {
      this.addResult('FAIL', 'Checksum save', `保存校验和失败: ${error.message}`)
    }
  }

  /**
   * 验证审计日志
   */
  async validateAuditLogs() {
    console.log('📋 验证审计日志...')

    try {
      // 检查审计日志表是否存在
      const [tables] = await this.connection.execute("SHOW TABLES LIKE 'audit_logs'")

      if (tables.length === 0) {
        this.addResult('WARN', 'Audit logs', '审计日志表不存在')
        return
      }

      // 检查最近的审计日志
      const [recentLogs] = await this.connection.execute(
        'SELECT COUNT(*) as count FROM audit_logs WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)'
      )

      this.addResult('INFO', 'Audit logs',
        `最近1小时内有 ${recentLogs[0].count} 条审计记录`)

      // 检查审计日志完整性
      const [logTypes] = await this.connection.execute(
        'SELECT action, COUNT(*) as count FROM audit_logs GROUP BY action'
      )

      logTypes.forEach(log => {
        this.addResult('INFO', 'Audit log type', `${log.action}: ${log.count} 条记录`)
      })

    } catch (error) {
      this.addResult('FAIL', 'Audit logs', `审计日志验证失败: ${error.message}`)
    }
  }

  /**
   * 验证备份完整性
   */
  async validateBackupIntegrity() {
    console.log('💾 验证备份完整性...')

    // 这里可以添加备份文件检查逻辑
    // 例如检查备份文件是否存在、是否完整等
    this.addResult('INFO', 'Backup integrity', '备份完整性检查需要配置备份路径')
  }

  /**
   * 验证性能指标
   */
  async validatePerformanceMetrics() {
    console.log('⚡ 验证性能指标...')

    try {
      // 检查表大小
      const [tableSizes] = await this.connection.execute(
        `SELECT table_name, ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
         FROM information_schema.tables
         WHERE table_schema = ?
         ORDER BY size_mb DESC`,
        [this.config.database]
      )

      tableSizes.forEach(table => {
        this.addResult('INFO', 'Table size', `${table.table_name}: ${table.size_mb} MB`)
      })

      // 检查索引使用情况
      const [indexes] = await this.connection.execute(
        `SELECT table_name, index_name, COUNT(*) as columns
         FROM information_schema.statistics
         WHERE table_schema = ?
         GROUP BY table_name, index_name
         ORDER BY table_name, index_name`,
        [this.config.database]
      )

      indexes.forEach(index => {
        this.addResult('INFO', 'Index', `${index.table_name}.${index.index_name} (${index.columns} columns)`)
      })

    } catch (error) {
      this.addResult('FAIL', 'Performance metrics', `性能指标验证失败: ${error.message}`)
    }
  }

  /**
   * 添加验证结果
   */
  addResult(level, category, message) {
    this.validationResults.push({
      timestamp: new Date().toISOString(),
      level,
      category,
      message
    })

    const icons = {
      PASS: '✅',
      FAIL: '❌',
      WARN: '⚠️',
      INFO: 'ℹ️',
      ERROR: '🚨'
    }

    console.log(`${icons[level] || ''} [${level}] ${category}: ${message}`)
  }

  /**
   * 生成验证报告
   */
  generateReport() {
    const summary = {
      total: this.validationResults.length,
      pass: this.validationResults.filter(r => r.level === 'PASS').length,
      fail: this.validationResults.filter(r => r.level === 'FAIL').length,
      warn: this.validationResults.filter(r => r.level === 'WARN').length,
      info: this.validationResults.filter(r => r.level === 'INFO').length,
      error: this.validationResults.filter(r => r.level === 'ERROR').length
    }

    const report = {
      timestamp: new Date().toISOString(),
      database: this.config.database,
      summary,
      results: this.validationResults,
      checksums: Object.fromEntries(this.checksums),
      status: summary.fail === 0 ? 'HEALTHY' : 'ISSUES_FOUND'
    }

    // 保存报告到文件
    this.saveReport(report)

    return report
  }

  /**
   * 保存报告到文件
   */
  async saveReport(report) {
    try {
      const reportPath = path.join(process.cwd(), 'database_integrity_report.json')
      await fs.writeFile(reportPath, JSON.stringify(report, null, 2))
      console.log(`📄 完整性报告已保存到: ${reportPath}`)
    } catch (error) {
      console.error('❌ 保存报告失败:', error.message)
    }
  }
}

export default DatabaseIntegrityValidator

// 如果直接运行此文件
if (import.meta.url === `file://${process.argv[1]}`) {
  const config = {
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'endo_sight_uc'
  }

  const validator = new DatabaseIntegrityValidator(config)

  validator.connect().then(async (connected) => {
    if (connected) {
      const report = await validator.validateAll()
      console.log('\n📊 验证完成:', report.status)
      console.log(`✅ 通过: ${report.summary.pass}`)
      console.log(`❌ 失败: ${report.summary.fail}`)
      console.log(`⚠️ 警告: ${report.summary.warn}`)

      await validator.disconnect()
      process.exit(report.summary.fail > 0 ? 1 : 0)
    } else {
      process.exit(1)
    }
  })
}